package Dao.impl;

import Dao.UserDao;
import Wrapper.OrderWrapper;
import Wrapper.TicketsWrapper;
import entity.Orders;
import entity.Ticket;
import entity.Train;
import entity.User;
import untils.Msg;
import untils.SqlCollection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class UserDaoImpl implements UserDao {
    private SqlCollection sqlCollection = new SqlCollection();
    @Override
    public Msg<ArrayList<Orders>> findPaymentOrder(int userId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        Msg<ArrayList<Orders>> order = new Msg<>();
        ArrayList<Orders> ordersList = new ArrayList<>();
        try{
            conn = sqlCollection.openConnection();
            String sql = "select * from orders inner join tickets on orders.ticket_id = tickets.ticket_id inner join trains on orders.train_id = trains.train_id ";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            Orders orders = null;
            while (rs.next()){
                OrderWrapper.OrderWrapper(rs,ordersList);
            }
            order.setData(ordersList);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlCollection.closeConnection(conn,pst,rs);
        }
        return order;
    }

    @Override
    public Msg<ArrayList<Orders>> findNotPayment(int userId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        Msg<ArrayList<Orders>> order = new Msg<>();
        ArrayList<Orders> ordersList = new ArrayList<>();
        try{
            conn = sqlCollection.openConnection();
            String sql = "select * from orders inner join tickets on orders.ticket_id = tickets.ticket_id and orders.status = ? inner join trains on orders.train_id = trains.train_id ";
            pst = conn.prepareStatement(sql);
            pst.setString(1,"未支付");
            rs = pst.executeQuery();
            Orders orders = null;
            while (rs.next()){
               OrderWrapper.OrderWrapper(rs,ordersList);
            }
            order.setData(ordersList);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlCollection.closeConnection(conn,pst,rs);
        }
        return order;
    }

    @Override
    public void deleteOrder(int orderId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;

        try {
            conn = sqlCollection.openConnection();
            String sql = "DELETE FROM orders WHERE order_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, orderId); // 假设order_id是表中的列名
            int affectedRows = pst.executeUpdate();
            if (affectedRows>0){
                Msg.msg = "订单已被删除";
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlCollection.closeConnection();
        }
    }

    @Override
    public void deleteTickets(int ticketId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;

        try {
            conn = sqlCollection.openConnection();
            String sql = "DELETE FROM tickets WHERE ticket_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, ticketId); // 假设order_id是表中的列名
            int affectedRows = pst.executeUpdate();
            if (affectedRows>0){
                Msg.msg = "订单已被删除";
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlCollection.closeConnection();
        }
    }

    //查询购票时保存的用户信息
    @Override
    public ArrayList<User> findUserData(int userId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        ArrayList<User> userData = new ArrayList<>();
        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from userdata where user_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, userId);
            rs = pst.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUser_id(rs.getInt("user_id"));
                user.setReal_name(rs.getString("real_name"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setIdCard(rs.getString("idcard"));
                userData.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlCollection.closeConnection(conn, pst, rs);
        }
        return userData;
    }

    @Override
    public void addUserData(int userId,String uName,String otherphone,String idCard) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "insert into userdata(user_id,real_name, phone,idcard) values(?,?,?,?)";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, userId);
            pst.setString(2, uName);
            pst.setString(3, otherphone);
            pst.setString(4, idCard);
            int res = pst.executeUpdate();
            if (res > 0) {
                Msg.msg = "存入成功";
            } else {
                Msg.msg = "FAILED";
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlCollection.closeConnection(conn, pst, rs);
        }

    }

    @Override
    public ArrayList<Ticket> findMyTickets(String idCard) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        ArrayList<Ticket> tickets = new ArrayList<>();
        try{
            conn = sqlCollection.openConnection();
            String sql = "select * from trains right join tickets on trains.train_id = tickets.train_id where tickets.idcard = ? and tickets.logic_delete = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,idCard);
            pst.setInt(2,0);
            rs = pst.executeQuery();
            while (rs.next()){
                tickets = TicketsWrapper.TicketsWrapper(rs,tickets);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return tickets;
    }

    @Override
    public ArrayList<User> findPageUserList(int firstCount, int pageSize) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            //1.打开数据库连接
            conn =sqlCollection.openConnection();
            //2.写sql，如果需要动态参数，使用占位符？
            String sql = "select * from user limit ?,?";
            //3.创建PreparedStatement预编译对象：PreparedStatement pst =  conn.prepareStatement(sql)
            pst = conn.prepareStatement(sql);
            //4.如果sql中有占位符就给占位符赋值。
            //   setXXX(int parameterIndex,XXX value)   第一个参数: 第几个占位符  第二参数：实参   XXX:类型
            //  如果sql中没有占位符，忽略此步骤
            pst.setInt(1,firstCount);
            pst.setInt(2,pageSize);
            //5.执行sql
            //   增删改：executeUpdate()---int 受影响的行数
            //   查询：executeQuery()---ResultSet结果集
            rs = pst.executeQuery();
            //6.处理ResultSet结果集--只有查询才执行此步骤
            //   (1)选择数据类型
            //       1.如果查询结果是单个数值，就使用对应的数据类型。 eg:总人数--int , 某个人的姓名--String, 最高工资--double
            //       2.如果查询结果是某条记录，就使用对应的实体类型。 eg:lily的用户信息-UserEntity，某个商品的信息--GoodsEntity
            //       3.如果查询结果是多条记录，就使用集合。eg:所有员工信息-ArrayList<EmployeeEntity> , 所有图书信息---ArrayList<BookEntity>
            //    (2)遍历  boolean next():将光标从当前位置向下移动一行
            //    (3)从结果中读取数据  getXXX(int colIndex/String colLabel):根据参数指定列的位置/列的名称获取对应类型的值。
            ArrayList<User> users = new ArrayList<>();
            User user = null;
            while (rs.next()){
                user = new User();
                user.setUser_id(rs.getInt("user_id"));
                user.setUserName(rs.getString("username"));
                user.setPhone(rs.getString("phone"));
                user.setPassword(rs.getString("password"));
                user.setReal_name(rs.getString("real_name"));
                user.setEmail(rs.getString("email"));
                user.setRole(rs.getString("role"));
                users.add(user); //把当前遍历出来的学生对象添加到集合中
            }
            return users ;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.关闭资源 close()
            sqlCollection.closeConnection(conn,pst,rs);
        }
        return null;
    }

    @Override
    public int findStudentTolalCount() {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            //1.打开数据库连接
            conn =sqlCollection.openConnection();
            //2.写sql，如果需要动态参数，使用占位符？
            String sql = "select count(*) from user";
            //3.创建PreparedStatement预编译对象：PreparedStatement pst =  conn.prepareStatement(sql)
            pst = conn.prepareStatement(sql);
            //4.如果sql中有占位符就给占位符赋值。
            //   setXXX(int parameterIndex,XXX value)   第一个参数: 第几个占位符  第二参数：实参   XXX:类型
            //  如果sql中没有占位符，忽略此步骤
            //5.执行sql
            //   增删改：executeUpdate()---int 受影响的行数
            //   查询：executeQuery()---ResultSet结果集
            rs = pst.executeQuery();
            //6.处理ResultSet结果集--只有查询才执行此步骤
            //   (1)选择数据类型
            //       1.如果查询结果是单个数值，就使用对应的数据类型。 eg:总人数--int , 某个人的姓名--String, 最高工资--double
            //       2.如果查询结果是某条记录，就使用对应的实体类型。 eg:lily的用户信息-UserEntity，某个商品的信息--GoodsEntity
            //       3.如果查询结果是多条记录，就使用集合。eg:所有员工信息-ArrayList<EmployeeEntity> , 所有图书信息---ArrayList<BookEntity>
            //    (2)遍历  boolean next():将光标从当前位置向下移动一行
            //    (3)从结果中读取数据  getXXX(int colIndex/String colLabel):根据参数指定列的位置/列的名称获取对应类型的值。
            int totalCount = rs.next()==true?rs.getInt(1):0;
            return totalCount;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.关闭资源 close()
            sqlCollection.closeConnection(conn,pst,rs);
        }
        return 0;
    }
}
